{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "877b9ecc",
   "metadata": {},
   "source": [
    "### 1、案例介绍"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88167df2",
   "metadata": {},
   "source": [
    "输入天气数据的Excel文件  \n",
    "存储到MySQL数据库（需要新建库、新建表等）"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8024fb5e",
   "metadata": {},
   "source": [
    "### 2、读取待处理Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dc0d7517",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e9477ed4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"北京2021年天气数据.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0520d21c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1级</td>\n",
       "      <td>54</td>\n",
       "      <td>良</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0</td>\n",
       "      <td>-9</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1级</td>\n",
       "      <td>78</td>\n",
       "      <td>良</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>-1</td>\n",
       "      <td>-8</td>\n",
       "      <td>多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1级</td>\n",
       "      <td>31</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>0</td>\n",
       "      <td>-11</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "      <td>3级</td>\n",
       "      <td>36</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-05</td>\n",
       "      <td>-2</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "      <td>2级</td>\n",
       "      <td>32</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd  high  low tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "0  2021-01-01     1  -11      晴       东北风     1级   54       良         2\n",
       "1  2021-01-02     0   -9   晴~多云       东北风     1级   78       良         2\n",
       "2  2021-01-03    -1   -8     多云       东北风     1级   31       优         1\n",
       "3  2021-01-04     0  -11   多云~晴       西北风     3级   36       优         1\n",
       "4  2021-01-05    -2  -11      晴       西北风     2级   32       优         1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "063c48a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[[\"ymd\", \"high\", \"low\", \"tianqi\", \"fengxiang\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6320a640",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0</td>\n",
       "      <td>-9</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>-1</td>\n",
       "      <td>-8</td>\n",
       "      <td>多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>0</td>\n",
       "      <td>-11</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-05</td>\n",
       "      <td>-2</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd  high  low tianqi fengxiang\n",
       "0  2021-01-01     1  -11      晴       东北风\n",
       "1  2021-01-02     0   -9   晴~多云       东北风\n",
       "2  2021-01-03    -1   -8     多云       东北风\n",
       "3  2021-01-04     0  -11   多云~晴       西北风\n",
       "4  2021-01-05    -2  -11      晴       西北风"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "26dfb091",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 365 entries, 0 to 364\n",
      "Data columns (total 5 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   ymd        365 non-null    object\n",
      " 1   high       365 non-null    int64 \n",
      " 2   low        365 non-null    int64 \n",
      " 3   tianqi     365 non-null    object\n",
      " 4   fengxiang  365 non-null    object\n",
      "dtypes: int64(2), object(3)\n",
      "memory usage: 14.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5290313e",
   "metadata": {},
   "source": [
    "### 3、怎样链接mysql数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9cbb7c04",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "49a1be75",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = pymysql.Connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user='root',\n",
    "    passwd='',\n",
    "    port=3306,\n",
    "    charset='utf8'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "91620984",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymysql.connections.Connection at 0x1f24d822b80>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c158259",
   "metadata": {},
   "source": [
    "### 4、怎样执行mysql命令"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4a3110a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_showdb = \"show databases;\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d3d423c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('information_schema',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('sys',)\n",
      "('test',)\n"
     ]
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(sql_showdb)\n",
    "for row in cursor.fetchall():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8a1e24c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Database</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>information_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mysql</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>performance_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Database\n",
       "0  information_schema\n",
       "1               mysql\n",
       "2  performance_schema\n",
       "3                 sys\n",
       "4                test"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(sql_showdb, con=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4ac9f97",
   "metadata": {},
   "source": [
    "### 5、新建数据库和数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c39fe0af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create database\n",
    "\n",
    "sql_create_db = \"\"\"\n",
    "    create database pyexcel\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e74a83cb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 365 entries, 0 to 364\n",
      "Data columns (total 5 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   ymd        365 non-null    object\n",
      " 1   high       365 non-null    int64 \n",
      " 2   low        365 non-null    int64 \n",
      " 3   tianqi     365 non-null    object\n",
      " 4   fengxiang  365 non-null    object\n",
      "dtypes: int64(2), object(3)\n",
      "memory usage: 14.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "71609114",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create table\n",
    "\n",
    "sql_create_table = \"\"\"\n",
    "    create table beijing_tianqi (\n",
    "        ymd char(10) comment '日期',\n",
    "        high int(11) comment '高温',\n",
    "        low int(11) comment '低温',\n",
    "        tianqi varchar(50) comment '天气',\n",
    "        fengxiang varchar(50) comment '风向',\n",
    "        primary key (ymd)\n",
    "    ) default char set utf8 comment '北京天气'\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9bb6bb09",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(sql_create_db)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5fd5059d",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = pymysql.Connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user='root',\n",
    "    passwd='',\n",
    "    port=3306,\n",
    "    charset='utf8',\n",
    "    db='pyexcel'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "99d27c42",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(sql_create_table)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55a2d48d",
   "metadata": {},
   "source": [
    "### 6、查询mysql数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7b60e1c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tables_in_pyexcel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>beijing_tianqi</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tables_in_pyexcel\n",
       "0    beijing_tianqi"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"show tables\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d1bff1a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Field</th>\n",
       "      <th>Type</th>\n",
       "      <th>Null</th>\n",
       "      <th>Key</th>\n",
       "      <th>Default</th>\n",
       "      <th>Extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ymd</td>\n",
       "      <td>char(10)</td>\n",
       "      <td>NO</td>\n",
       "      <td>PRI</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>high</td>\n",
       "      <td>int(11)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>low</td>\n",
       "      <td>int(11)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tianqi</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fengxiang</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Field         Type Null  Key Default Extra\n",
       "0        ymd     char(10)   NO  PRI    None      \n",
       "1       high      int(11)  YES         None      \n",
       "2        low      int(11)  YES         None      \n",
       "3     tianqi  varchar(50)  YES         None      \n",
       "4  fengxiang  varchar(50)  YES         None      "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"desc beijing_tianqi\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5ab371de",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [ymd, high, low, tianqi, fengxiang]\n",
       "Index: []"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"select * from beijing_tianqi\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8118e89a",
   "metadata": {},
   "source": [
    "### 7、新增一条数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "04424716",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0</td>\n",
       "      <td>-9</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>-1</td>\n",
       "      <td>-8</td>\n",
       "      <td>多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>0</td>\n",
       "      <td>-11</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-05</td>\n",
       "      <td>-2</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd  high  low tianqi fengxiang\n",
       "0  2021-01-01     1  -11      晴       东北风\n",
       "1  2021-01-02     0   -9   晴~多云       东北风\n",
       "2  2021-01-03    -1   -8     多云       东北风\n",
       "3  2021-01-04     0  -11   多云~晴       西北风\n",
       "4  2021-01-05    -2  -11      晴       西北风"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2bd64e93",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "    insert into beijing_tianqi\n",
    "    (ymd, high, low, tianqi, fengxiang)\n",
    "    values('2021-01-01', 1, -11, '晴', '东北风')\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "666bb231",
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_db(sql):\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(sql)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ce2b66f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "write_db(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "75837d6f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd  high  low tianqi fengxiang\n",
       "0  2021-01-01     1  -11      晴       东北风"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"select * from beijing_tianqi\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78b1b984",
   "metadata": {},
   "source": [
    "### 8、删除一条数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2b1e9971",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "    delete from beijing_tianqi\n",
    "    where ymd='2021-01-01'\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "3c6e288f",
   "metadata": {},
   "outputs": [],
   "source": [
    "write_db(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "a8486fcb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [ymd, high, low, tianqi, fengxiang]\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"select * from beijing_tianqi\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6292a971",
   "metadata": {},
   "source": [
    "### 9、批量新增数据到mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a32b6bb0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0</td>\n",
       "      <td>-9</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>-1</td>\n",
       "      <td>-8</td>\n",
       "      <td>多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>0</td>\n",
       "      <td>-11</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-05</td>\n",
       "      <td>-2</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd  high  low tianqi fengxiang\n",
       "0  2021-01-01     1  -11      晴       东北风\n",
       "1  2021-01-02     0   -9   晴~多云       东北风\n",
       "2  2021-01-03    -1   -8     多云       东北风\n",
       "3  2021-01-04     0  -11   多云~晴       西北风\n",
       "4  2021-01-05    -2  -11      晴       西北风"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "97780d7f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-01-01 1 -11 晴 东北风\n",
      "2021-01-02 0 -9 晴~多云 东北风\n",
      "2021-01-03 -1 -8 多云 东北风\n",
      "2021-01-04 0 -11 多云~晴 西北风\n",
      "2021-01-05 -2 -11 晴 西北风\n",
      "2021-01-06 0 -19 多云~晴 西北风\n",
      "2021-01-07 -7 -14 晴 西北风\n",
      "2021-01-08 -2 -10 晴 西北风\n",
      "2021-01-09 1 -8 晴 西北风\n",
      "2021-01-10 1 -7 多云 西北风\n",
      "2021-01-11 2 -8 多云 西北风\n",
      "2021-01-12 4 -6 晴~多云 北风\n",
      "2021-01-13 8 -6 晴~多云 北风\n",
      "2021-01-14 2 -4 阴~多云 东北风\n",
      "2021-01-15 3 -8 晴 北风\n",
      "2021-01-16 0 -8 晴 西北风\n",
      "2021-01-17 3 -7 晴 西风\n",
      "2021-01-18 4 -6 晴~阴 东北风\n",
      "2021-01-19 -3 -8 阴~多云 东北风\n",
      "2021-01-20 1 -5 晴~多云 西北风\n",
      "2021-01-21 5 -5 阴~晴 西北风\n",
      "2021-01-22 5 -7 晴~多云 东北风\n",
      "2021-01-23 3 -6 多云 西南风\n",
      "2021-01-24 5 -5 晴~多云 东北风\n",
      "2021-01-25 1 -6 雨夹雪~雾 西风\n",
      "2021-01-26 6 -7 阴~多云 西北风\n",
      "2021-01-27 3 -6 阴~多云 北风\n",
      "2021-01-28 -1 -9 晴 西北风\n",
      "2021-01-29 0 -8 晴 东北风\n",
      "2021-01-30 7 -7 晴 东南风\n",
      "2021-01-31 0 -5 多云~晴 东北风\n",
      "2021-02-01 2 -10 晴 西北风\n",
      "2021-02-02 0 -8 晴~多云 西北风\n",
      "2021-02-03 7 -6 多云~晴 西北风\n",
      "2021-02-04 10 -5 晴~多云 西南风\n",
      "2021-02-05 15 -2 晴 西北风\n",
      "2021-02-06 16 -3 晴~多云 西北风\n",
      "2021-02-07 7 -5 晴 北风\n",
      "2021-02-08 7 -5 多云 东南风\n",
      "2021-02-09 10 -3 晴 北风\n",
      "2021-02-10 15 -3 晴 北风\n",
      "2021-02-11 11 -1 多云~晴 北风\n",
      "2021-02-12 14 -1 多云 东北风\n",
      "2021-02-13 7 2 雾~小雨 东北风\n",
      "2021-02-14 4 -5 雨夹雪~多云 东北风\n",
      "2021-02-15 5 -4 晴~多云 西风\n",
      "2021-02-16 0 -7 晴 西北风\n",
      "2021-02-17 3 -7 晴 西北风\n",
      "2021-02-18 7 -5 晴 东北风\n",
      "2021-02-19 16 -2 多云 西北风\n",
      "2021-02-20 20 3 阴~多云 西北风\n",
      "2021-02-21 25 4 阴~晴 西北风\n",
      "2021-02-22 12 0 多云 东北风\n",
      "2021-02-23 3 -3 阴 东南风\n",
      "2021-02-24 5 -3 阴~多云 东南风\n",
      "2021-02-25 8 -3 阴~晴 东北风\n",
      "2021-02-26 11 -3 多云~晴 南风\n",
      "2021-02-27 11 1 多云~雨夹雪 南风\n",
      "2021-02-28 5 0 小雨~雨夹雪 东北风\n",
      "2021-03-01 5 -6 小雪~多云 南风\n",
      "2021-03-02 4 -2 多云 南风\n",
      "2021-03-03 6 2 多云~阴 东北风\n",
      "2021-03-04 11 2 阴~多云 东北风\n",
      "2021-03-05 9 -2 雾~多云 东南风\n",
      "2021-03-06 4 -5 阴~晴 南风\n",
      "2021-03-07 8 -2 多云 西南风\n",
      "2021-03-08 10 1 多云 西南风\n",
      "2021-03-09 15 3 多云 北风\n",
      "2021-03-10 16 8 雾 西南风\n",
      "2021-03-11 17 7 阴 南风\n",
      "2021-03-12 10 6 小雨~雾 东南风\n",
      "2021-03-13 14 8 多云 东南风\n",
      "2021-03-14 13 9 阴 南风\n",
      "2021-03-15 12 2 扬沙~浮尘 西北风\n",
      "2021-03-16 15 3 浮尘 北风\n",
      "2021-03-17 13 6 小雨 东南风\n",
      "2021-03-18 8 3 阴~多云 东南风\n",
      "2021-03-19 10 5 阴~小雨 西南风\n",
      "2021-03-20 13 3 浮尘~晴 西北风\n",
      "2021-03-21 12 1 晴 西北风\n",
      "2021-03-22 19 5 晴 西南风\n",
      "2021-03-23 23 8 晴 西南风\n",
      "2021-03-24 21 6 阴~晴 东北风\n",
      "2021-03-25 21 11 多云 西南风\n",
      "2021-03-26 14 10 小雨~雾 东南风\n",
      "2021-03-27 18 10 雾~浮尘 南风\n",
      "2021-03-28 18 8 扬沙~晴 西北风\n",
      "2021-03-29 19 6 多云~晴 西北风\n",
      "2021-03-30 21 9 多云~晴 西南风\n",
      "2021-03-31 24 10 多云 东北风\n",
      "2021-04-01 18 12 阴 东南风\n",
      "2021-04-02 16 10 阴~小雨 东南风\n",
      "2021-04-03 18 7 晴 东北风\n",
      "2021-04-04 19 7 晴~多云 西南风\n",
      "2021-04-05 18 8 阴~多云 西南风\n",
      "2021-04-06 21 10 多云 西南风\n",
      "2021-04-07 19 5 晴 东南风\n",
      "2021-04-08 19 8 晴~多云 东南风\n",
      "2021-04-09 18 6 多云 东南风\n",
      "2021-04-10 20 12 阴~多云 南风\n",
      "2021-04-11 20 9 阴 南风\n",
      "2021-04-12 16 8 多云 西北风\n",
      "2021-04-13 16 5 晴 西北风\n",
      "2021-04-14 22 10 晴~多云 西南风\n",
      "2021-04-15 21 8 扬沙 西风\n",
      "2021-04-16 17 9 多云~晴 西北风\n",
      "2021-04-17 20 5 晴 西北风\n",
      "2021-04-18 25 10 晴 西南风\n",
      "2021-04-19 27 15 晴~多云 西南风\n",
      "2021-04-20 26 13 阴~多云 南风\n",
      "2021-04-21 21 12 阴~小雨 南风\n",
      "2021-04-22 16 9 小雨~多云 东南风\n",
      "2021-04-23 21 12 多云 东南风\n",
      "2021-04-24 18 13 阴~多云 东南风\n",
      "2021-04-25 20 10 阴 南风\n",
      "2021-04-26 22 8 阴~浮尘 南风\n",
      "2021-04-27 22 11 扬沙~晴 西北风\n",
      "2021-04-28 22 10 阴~多云 西南风\n",
      "2021-04-29 18 8 多云 西北风\n",
      "2021-04-30 14 6 阴~晴 西北风\n",
      "2021-05-01 20 7 晴 北风\n",
      "2021-05-02 21 13 阴~晴 南风\n",
      "2021-05-03 24 13 晴~多云 西南风\n",
      "2021-05-04 23 10 多云 西北风\n",
      "2021-05-05 27 16 晴 西南风\n",
      "2021-05-06 19 14 多云~扬沙 西风\n",
      "2021-05-07 25 17 晴 西北风\n",
      "2021-05-08 26 12 多云 西北风\n",
      "2021-05-09 26 13 多云 东南风\n",
      "2021-05-10 23 13 阴~多云 东南风\n",
      "2021-05-11 24 13 多云 东南风\n",
      "2021-05-12 26 15 多云 东北风\n",
      "2021-05-13 23 16 阴~多云 东南风\n",
      "2021-05-14 23 18 多云~阴 东南风\n",
      "2021-05-15 19 15 小雨 东北风\n",
      "2021-05-16 26 13 阴~晴 西北风\n",
      "2021-05-17 31 15 晴 西南风\n",
      "2021-05-18 32 19 多云 西南风\n",
      "2021-05-19 30 19 阴 西南风\n",
      "2021-05-20 28 16 阴~多云 西南风\n",
      "2021-05-21 30 20 晴~雷阵雨 西南风\n",
      "2021-05-22 32 16 阴~雷阵雨 西南风\n",
      "2021-05-23 26 13 扬沙~晴 西北风\n",
      "2021-05-24 24 13 多云 西北风\n",
      "2021-05-25 28 15 多云 西南风\n",
      "2021-05-26 28 13 阴~晴 北风\n",
      "2021-05-27 28 16 雷阵雨~多云 西北风\n",
      "2021-05-28 29 16 晴~多云 西北风\n",
      "2021-05-29 27 15 晴~小雨 东风\n",
      "2021-05-30 27 15 晴~小雨 东南风\n",
      "2021-05-31 19 14 雷阵雨~小雨 东风\n",
      "2021-06-01 28 17 多云~雷阵雨 东南风\n",
      "2021-06-02 29 14 多云~晴 北风\n",
      "2021-06-03 28 17 晴 西北风\n",
      "2021-06-04 30 16 晴 西北风\n",
      "2021-06-05 34 18 晴 西北风\n",
      "2021-06-06 33 20 多云~小雨 东风\n",
      "2021-06-07 27 19 阴 东南风\n",
      "2021-06-08 29 21 阴~小雨 东南风\n",
      "2021-06-09 25 19 阴~中雨 西北风\n",
      "2021-06-10 32 18 阴~晴 西风\n",
      "2021-06-11 36 20 阴~多云 西南风\n",
      "2021-06-12 36 24 阴~多云 南风\n",
      "2021-06-13 34 23 阴~雷阵雨 南风\n",
      "2021-06-14 25 23 小雨~雷阵雨 东北风\n",
      "2021-06-15 30 21 阴 东南风\n",
      "2021-06-16 22 20 阴~小雨 东南风\n",
      "2021-06-17 32 19 晴 西北风\n",
      "2021-06-18 34 19 阴~晴 西北风\n",
      "2021-06-19 37 20 晴 西北风\n",
      "2021-06-20 36 21 晴 西北风\n",
      "2021-06-21 35 21 多云 东北风\n",
      "2021-06-22 32 21 晴~小雨 南风\n",
      "2021-06-23 25 22 阴~小雨 东南风\n",
      "2021-06-24 27 20 阴~小雨 南风\n",
      "2021-06-25 28 22 小雨~雷阵雨 东南风\n",
      "2021-06-26 28 23 多云 东北风\n",
      "2021-06-27 33 23 多云 东风\n",
      "2021-06-28 30 22 阴~雷阵雨 东南风\n",
      "2021-06-29 27 22 阴~雷阵雨 东南风\n",
      "2021-06-30 30 23 阴 东风\n",
      "2021-07-01 30 22 雷阵雨 西南风\n",
      "2021-07-02 30 19 阴~大雨 南风\n",
      "2021-07-03 28 21 雷阵雨 东风\n",
      "2021-07-04 33 21 多云 西南风\n",
      "2021-07-05 32 21 阴~中雨 东风\n",
      "2021-07-06 30 22 多云 西南风\n",
      "2021-07-07 32 23 多云 东南风\n",
      "2021-07-08 33 22 晴~多云 北风\n",
      "2021-07-09 32 23 多云~晴 东风\n",
      "2021-07-10 31 23 多云~雷阵雨 东南风\n",
      "2021-07-11 30 23 阴~大雨 东南风\n",
      "2021-07-12 25 21 暴雨~小雨 东风\n",
      "2021-07-13 31 24 阴~多云 北风\n",
      "2021-07-14 32 24 阴~雷阵雨 东南风\n",
      "2021-07-15 32 24 阴 东南风\n",
      "2021-07-16 30 24 阴~中雨 东南风\n",
      "2021-07-17 29 23 阴~雷阵雨 东北风\n",
      "2021-07-18 27 23 中雨~雷阵雨 东北风\n",
      "2021-07-19 30 23 阴~雷阵雨 东南风\n",
      "2021-07-20 31 23 阴~多云 东南风\n",
      "2021-07-21 28 23 阴~雷阵雨 东南风\n",
      "2021-07-22 28 23 小雨~雷阵雨 东南风\n",
      "2021-07-23 31 23 晴~多云 南风\n",
      "2021-07-24 32 24 多云 南风\n",
      "2021-07-25 33 25 阴~多云 南风\n",
      "2021-07-26 32 25 阴~雷阵雨 南风\n",
      "2021-07-27 27 24 阴~雷阵雨 东北风\n",
      "2021-07-28 28 24 阴~雷阵雨 东北风\n",
      "2021-07-29 26 22 中雨~大雨 东北风\n",
      "2021-07-30 30 22 阴~晴 西北风\n",
      "2021-07-31 34 23 多云~雷阵雨 东北风\n",
      "2021-08-01 32 23 晴 北风\n",
      "2021-08-02 33 23 晴~多云 东北风\n",
      "2021-08-03 31 24 阴~雷阵雨 南风\n",
      "2021-08-04 31 23 阴~多云 东南风\n",
      "2021-08-05 32 23 阴~多云 东南风\n",
      "2021-08-06 33 22 阴~晴 东南风\n",
      "2021-08-07 33 23 阴~多云 东南风\n",
      "2021-08-08 31 23 阴~雷阵雨 南风\n",
      "2021-08-09 30 22 多云~雷阵雨 东北风\n",
      "2021-08-10 33 23 多云~晴 东南风\n",
      "2021-08-11 30 23 多云~雷阵雨 东北风\n",
      "2021-08-12 29 22 阴~雷阵雨 东南风\n",
      "2021-08-13 29 22 阴~雷阵雨 东南风\n",
      "2021-08-14 28 22 阴~雷阵雨 东南风\n",
      "2021-08-15 28 22 阴~雷阵雨 东南风\n",
      "2021-08-16 28 22 阴~雷阵雨 东南风\n",
      "2021-08-17 30 21 多云 东风\n",
      "2021-08-18 28 23 阴~小雨 东南风\n",
      "2021-08-19 24 20 中雨~小雨 东南风\n",
      "2021-08-20 28 21 阴~晴 东北风\n",
      "2021-08-21 31 22 晴~多云 西南风\n",
      "2021-08-22 30 23 阴~雷阵雨 西南风\n",
      "2021-08-23 30 20 阴~中雨 西南风\n",
      "2021-08-24 27 19 多云 西北风\n",
      "2021-08-25 28 19 多云~晴 西北风\n",
      "2021-08-26 30 19 多云 西北风\n",
      "2021-08-27 30 18 晴 西北风\n",
      "2021-08-28 28 18 阴~多云 北风\n",
      "2021-08-29 28 20 阴~多云 西南风\n",
      "2021-08-30 28 20 多云~小雨 东南风\n",
      "2021-08-31 27 17 多云~晴 南风\n",
      "2021-09-01 29 18 晴 西南风\n",
      "2021-09-02 29 20 多云 西南风\n",
      "2021-09-03 30 22 多云~雷阵雨 东南风\n",
      "2021-09-04 24 19 多云 东南风\n",
      "2021-09-05 24 22 多云~中雨 东南风\n",
      "2021-09-06 23 14 多云~晴 北风\n",
      "2021-09-07 27 17 晴 西南风\n",
      "2021-09-08 28 19 多云~阴 东南风\n",
      "2021-09-09 27 18 多云~阴 东南风\n",
      "2021-09-10 29 19 多云 东北风\n",
      "2021-09-11 31 21 多云~中雨 东北风\n",
      "2021-09-12 23 20 多云~雷阵雨 东北风\n",
      "2021-09-13 29 20 多云~雷阵雨 东北风\n",
      "2021-09-14 25 17 多云 东南风\n",
      "2021-09-15 25 19 多云~小雨 南风\n",
      "2021-09-16 24 17 小雨~雾 东北风\n",
      "2021-09-17 27 17 多云~晴 东南风\n",
      "2021-09-18 24 19 多云~小雨 东南风\n",
      "2021-09-19 18 16 中雨 南风\n",
      "2021-09-20 21 15 中雨~多云 西北风\n",
      "2021-09-21 27 14 多云~晴 西北风\n",
      "2021-09-22 29 17 多云 西南风\n",
      "2021-09-23 27 17 多云~中雨 东南风\n",
      "2021-09-24 19 18 中雨 东北风\n",
      "2021-09-25 24 18 多云~小雨 东南风\n",
      "2021-09-26 20 18 小雨 东北风\n",
      "2021-09-27 28 17 雾 东北风\n",
      "2021-09-28 24 17 多云 南风\n",
      "2021-09-29 28 17 多云~小雨 东北风\n",
      "2021-09-30 22 16 多云 东北风\n",
      "2021-10-01 23 16 多云~小雨 东北风\n",
      "2021-10-02 21 17 多云~小雨 东风\n",
      "2021-10-03 21 15 小雨 北风\n",
      "2021-10-04 18 12 小雨 北风\n",
      "2021-10-05 15 12 多云~中雨 西北风\n",
      "2021-10-06 12 6 中雨~多云 东北风\n",
      "2021-10-07 19 7 多云~晴 南风\n",
      "2021-10-08 20 11 多云~小雨 东南风\n",
      "2021-10-09 14 12 小雨~晴 南风\n",
      "2021-10-10 18 6 多云~晴 西北风\n",
      "2021-10-11 19 5 晴 北风\n",
      "2021-10-12 20 9 晴 东风\n",
      "2021-10-13 20 11 多云~小雨 东南风\n",
      "2021-10-14 19 11 多云 西北风\n",
      "2021-10-15 20 5 多云~晴 北风\n",
      "2021-10-16 12 1 晴 西北风\n",
      "2021-10-17 15 2 晴 西南风\n",
      "2021-10-18 17 4 多云~晴 东北风\n",
      "2021-10-19 14 1 晴 东北风\n",
      "2021-10-20 14 3 多云~晴 西南风\n",
      "2021-10-21 19 5 晴 西北风\n",
      "2021-10-22 18 2 晴 西北风\n",
      "2021-10-23 18 3 晴 西南风\n",
      "2021-10-24 17 4 多云 西南风\n",
      "2021-10-25 18 7 多云 南风\n",
      "2021-10-26 22 5 多云~晴 北风\n",
      "2021-10-27 21 6 晴~多云 西北风\n",
      "2021-10-28 19 7 多云~小雨 东北风\n",
      "2021-10-29 19 6 多云~雾 东风\n",
      "2021-10-30 20 8 多云 西北风\n",
      "2021-10-31 18 5 多云~阴 西北风\n",
      "2021-11-01 13 6 多云~阴 南风\n",
      "2021-11-02 12 4 小雨~雾 西南风\n",
      "2021-11-03 12 5 多云~雾 东南风\n",
      "2021-11-04 14 7 多云 东北风\n",
      "2021-11-05 12 10 多云~小雨 东北风\n",
      "2021-11-06 12 -1 小雨~大雪 北风\n",
      "2021-11-07 1 -4 中雪~多云 西北风\n",
      "2021-11-08 7 -2 晴 西北风\n",
      "2021-11-09 11 1 晴 西北风\n",
      "2021-11-10 14 2 晴 西北风\n",
      "2021-11-11 13 0 晴 西北风\n",
      "2021-11-12 15 0 晴 西北风\n",
      "2021-11-13 18 1 晴 西北风\n",
      "2021-11-14 16 2 晴 西北风\n",
      "2021-11-15 11 0 晴 东北风\n",
      "2021-11-16 11 2 多云~雾 西南风\n",
      "2021-11-17 13 1 多云~晴 西风\n",
      "2021-11-18 9 2 多云 北风\n",
      "2021-11-19 10 3 多云~阴 东风\n",
      "2021-11-20 8 0 多云 东北风\n",
      "2021-11-21 7 -1 多云~晴 西北风\n",
      "2021-11-22 4 -5 晴 西北风\n",
      "2021-11-23 8 -4 晴 西南风\n",
      "2021-11-24 12 -2 多云~晴 西北风\n",
      "2021-11-25 13 -2 多云~晴 西北风\n",
      "2021-11-26 13 -3 多云~晴 北风\n",
      "2021-11-27 10 0 多云~阴 东北风\n",
      "2021-11-28 9 2 多云~阴 北风\n",
      "2021-11-29 7 -1 小雨~多云 北风\n",
      "2021-11-30 5 -5 多云~晴 西北风\n",
      "2021-12-01 9 -5 多云~晴 西风\n",
      "2021-12-02 13 0 多云~晴 西北风\n",
      "2021-12-03 13 -1 多云~晴 西北风\n",
      "2021-12-04 13 -3 多云~晴 北风\n",
      "2021-12-05 11 0 多云 西北风\n",
      "2021-12-06 12 -3 多云~晴 西北风\n",
      "2021-12-07 10 -3 多云 西北风\n",
      "2021-12-08 7 1 多云~小雨 东南风\n",
      "2021-12-09 4 -1 雾 东南风\n",
      "2021-12-10 9 -1 多云 北风\n",
      "2021-12-11 11 -3 多云~晴 西北风\n",
      "2021-12-12 3 -6 晴 西北风\n",
      "2021-12-13 4 -6 晴 西南风\n",
      "2021-12-14 6 -5 多云~晴 西北风\n",
      "2021-12-15 3 -4 多云~晴 东北风\n",
      "2021-12-16 4 -7 晴 西北风\n",
      "2021-12-17 0 -7 晴 西北风\n",
      "2021-12-18 5 -7 晴 西北风\n",
      "2021-12-19 9 -5 晴 西南风\n",
      "2021-12-20 13 -3 多云~晴 西北风\n",
      "2021-12-21 8 -2 多云~阴 东北风\n",
      "2021-12-22 5 -2 多云 东南风\n",
      "2021-12-23 -1 -5 多云~小雪 东风\n",
      "2021-12-24 -4 -10 晴 东北风\n",
      "2021-12-25 -4 -11 晴 西北风\n",
      "2021-12-26 -1 -8 晴 西北风\n",
      "2021-12-27 6 -8 晴 西北风\n",
      "2021-12-28 6 -5 多云~晴 西北风\n",
      "2021-12-29 5 -5 晴 西北风\n",
      "2021-12-30 6 -7 晴 西北风\n",
      "2021-12-31 5 -7 晴 东北风\n"
     ]
    }
   ],
   "source": [
    "for idx, row in df.iterrows():\n",
    "    ymd, high, low, tianqi, fengxiang = (\n",
    "        row[\"ymd\"], \n",
    "        row[\"high\"], \n",
    "        row[\"low\"], \n",
    "        row[\"tianqi\"], \n",
    "        row[\"fengxiang\"], \n",
    "    )\n",
    "    print(ymd, high, low, tianqi, fengxiang)\n",
    "    \n",
    "    sql = f\"\"\"\n",
    "        insert into beijing_tianqi\n",
    "        (ymd, high, low, tianqi, fengxiang)\n",
    "        values('{ymd}', {high}, {low}, '{tianqi}', '{fengxiang}')\n",
    "    \"\"\"\n",
    "    write_db(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "786fd8a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0</td>\n",
       "      <td>-9</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>-1</td>\n",
       "      <td>-8</td>\n",
       "      <td>多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>0</td>\n",
       "      <td>-11</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-05</td>\n",
       "      <td>-2</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>2021-12-27</td>\n",
       "      <td>6</td>\n",
       "      <td>-8</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>2021-12-28</td>\n",
       "      <td>6</td>\n",
       "      <td>-5</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>2021-12-29</td>\n",
       "      <td>5</td>\n",
       "      <td>-5</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>6</td>\n",
       "      <td>-7</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364</th>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>5</td>\n",
       "      <td>-7</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>365 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ymd  high  low tianqi fengxiang\n",
       "0    2021-01-01     1  -11      晴       东北风\n",
       "1    2021-01-02     0   -9   晴~多云       东北风\n",
       "2    2021-01-03    -1   -8     多云       东北风\n",
       "3    2021-01-04     0  -11   多云~晴       西北风\n",
       "4    2021-01-05    -2  -11      晴       西北风\n",
       "..          ...   ...  ...    ...       ...\n",
       "360  2021-12-27     6   -8      晴       西北风\n",
       "361  2021-12-28     6   -5   多云~晴       西北风\n",
       "362  2021-12-29     5   -5      晴       西北风\n",
       "363  2021-12-30     6   -7      晴       西北风\n",
       "364  2021-12-31     5   -7      晴       东北风\n",
       "\n",
       "[365 rows x 5 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"select * from beijing_tianqi\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50896f59",
   "metadata": {},
   "source": [
    "### 10、实现统计查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a25482ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ymd</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0</td>\n",
       "      <td>-9</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>-1</td>\n",
       "      <td>-8</td>\n",
       "      <td>多云</td>\n",
       "      <td>东北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>0</td>\n",
       "      <td>-11</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-05</td>\n",
       "      <td>-2</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-01-06</td>\n",
       "      <td>0</td>\n",
       "      <td>-19</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>-7</td>\n",
       "      <td>-14</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-01-08</td>\n",
       "      <td>-2</td>\n",
       "      <td>-10</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2021-01-09</td>\n",
       "      <td>1</td>\n",
       "      <td>-8</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2021-01-10</td>\n",
       "      <td>1</td>\n",
       "      <td>-7</td>\n",
       "      <td>多云</td>\n",
       "      <td>西北风</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd  high  low tianqi fengxiang\n",
       "0  2021-01-01     1  -11      晴       东北风\n",
       "1  2021-01-02     0   -9   晴~多云       东北风\n",
       "2  2021-01-03    -1   -8     多云       东北风\n",
       "3  2021-01-04     0  -11   多云~晴       西北风\n",
       "4  2021-01-05    -2  -11      晴       西北风\n",
       "5  2021-01-06     0  -19   多云~晴       西北风\n",
       "6  2021-01-07    -7  -14      晴       西北风\n",
       "7  2021-01-08    -2  -10      晴       西北风\n",
       "8  2021-01-09     1   -8      晴       西北风\n",
       "9  2021-01-10     1   -7     多云       西北风"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"select * from beijing_tianqi limit 10\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "531e8499",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max(high)</th>\n",
       "      <th>min(low)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37</td>\n",
       "      <td>-19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max(high)  min(low)\n",
       "0         37       -19"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"\n",
    "    select max(high), min(low)\n",
    "    from beijing_tianqi\n",
    "    \"\"\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5a0c9495",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tianqi</th>\n",
       "      <th>max(high)</th>\n",
       "      <th>min(low)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>中雨</td>\n",
       "      <td>19</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中雨~多云</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>中雨~大雨</td>\n",
       "      <td>26</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>中雨~小雨</td>\n",
       "      <td>24</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>中雨~雷阵雨</td>\n",
       "      <td>27</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>中雪~多云</td>\n",
       "      <td>1</td>\n",
       "      <td>-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>多云</td>\n",
       "      <td>35</td>\n",
       "      <td>-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>多云~中雨</td>\n",
       "      <td>31</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>多云~小雨</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>多云~小雪</td>\n",
       "      <td>-1</td>\n",
       "      <td>-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>多云~扬沙</td>\n",
       "      <td>19</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>多云~晴</td>\n",
       "      <td>33</td>\n",
       "      <td>-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>多云~阴</td>\n",
       "      <td>28</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>多云~雨夹雪</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>多云~雷阵雨</td>\n",
       "      <td>34</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>多云~雾</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>小雨</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>小雨~多云</td>\n",
       "      <td>16</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>小雨~大雪</td>\n",
       "      <td>12</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>小雨~晴</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>小雨~雨夹雪</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>小雨~雷阵雨</td>\n",
       "      <td>28</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>小雨~雾</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>小雪~多云</td>\n",
       "      <td>5</td>\n",
       "      <td>-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>扬沙</td>\n",
       "      <td>21</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>扬沙~晴</td>\n",
       "      <td>26</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>扬沙~浮尘</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>晴</td>\n",
       "      <td>37</td>\n",
       "      <td>-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>晴~多云</td>\n",
       "      <td>33</td>\n",
       "      <td>-9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>晴~小雨</td>\n",
       "      <td>32</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>晴~阴</td>\n",
       "      <td>4</td>\n",
       "      <td>-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>晴~雷阵雨</td>\n",
       "      <td>30</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>暴雨~小雨</td>\n",
       "      <td>25</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>浮尘</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>浮尘~晴</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>阴</td>\n",
       "      <td>32</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>阴~中雨</td>\n",
       "      <td>32</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>阴~多云</td>\n",
       "      <td>36</td>\n",
       "      <td>-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>阴~大雨</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>阴~小雨</td>\n",
       "      <td>29</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>阴~晴</td>\n",
       "      <td>34</td>\n",
       "      <td>-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>阴~浮尘</td>\n",
       "      <td>22</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>阴~雷阵雨</td>\n",
       "      <td>34</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>雨夹雪~多云</td>\n",
       "      <td>4</td>\n",
       "      <td>-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>雨夹雪~雾</td>\n",
       "      <td>1</td>\n",
       "      <td>-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>雷阵雨</td>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>雷阵雨~多云</td>\n",
       "      <td>28</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>雷阵雨~小雨</td>\n",
       "      <td>19</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>雾</td>\n",
       "      <td>28</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>雾~多云</td>\n",
       "      <td>9</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>雾~小雨</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>雾~浮尘</td>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    tianqi  max(high)  min(low)\n",
       "0       中雨         19        16\n",
       "1    中雨~多云         21         6\n",
       "2    中雨~大雨         26        22\n",
       "3    中雨~小雨         24        20\n",
       "4   中雨~雷阵雨         27        23\n",
       "5    中雪~多云          1        -4\n",
       "6       多云         35        -8\n",
       "7    多云~中雨         31        12\n",
       "8    多云~小雨         33         1\n",
       "9    多云~小雪         -1        -5\n",
       "10   多云~扬沙         19        14\n",
       "11    多云~晴         33       -19\n",
       "12    多云~阴         28        -2\n",
       "13  多云~雨夹雪         11         1\n",
       "14  多云~雷阵雨         34        17\n",
       "15    多云~雾         19         2\n",
       "16      小雨         21         6\n",
       "17   小雨~多云         16        -1\n",
       "18   小雨~大雪         12        -1\n",
       "19    小雨~晴         14        12\n",
       "20  小雨~雨夹雪          5         0\n",
       "21  小雨~雷阵雨         28        22\n",
       "22    小雨~雾         24         4\n",
       "23   小雪~多云          5        -6\n",
       "24      扬沙         21         8\n",
       "25    扬沙~晴         26         8\n",
       "26   扬沙~浮尘         12         2\n",
       "27       晴         37       -14\n",
       "28    晴~多云         33        -9\n",
       "29    晴~小雨         32        15\n",
       "30     晴~阴          4        -6\n",
       "31   晴~雷阵雨         30        20\n",
       "32   暴雨~小雨         25        21\n",
       "33      浮尘         15         3\n",
       "34    浮尘~晴         13         3\n",
       "35       阴         32        -3\n",
       "36    阴~中雨         32        19\n",
       "37    阴~多云         36        -8\n",
       "38    阴~大雨         30        19\n",
       "39    阴~小雨         29         5\n",
       "40     阴~晴         34        -5\n",
       "41    阴~浮尘         22         8\n",
       "42   阴~雷阵雨         34        16\n",
       "43  雨夹雪~多云          4        -5\n",
       "44   雨夹雪~雾          1        -6\n",
       "45     雷阵雨         30        21\n",
       "46  雷阵雨~多云         28        16\n",
       "47  雷阵雨~小雨         19        14\n",
       "48       雾         28        -1\n",
       "49    雾~多云          9        -2\n",
       "50    雾~小雨          7         2\n",
       "51    雾~浮尘         18        10"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"\n",
    "    select \n",
    "        tianqi, \n",
    "        max(high), \n",
    "        min(low)\n",
    "    from beijing_tianqi\n",
    "    group by tianqi\n",
    "    \"\"\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a6eb412",
   "metadata": {},
   "source": [
    "### 11、读取MySQL存入Excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e4000a13",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = pd.read_sql(\n",
    "    \"\"\"\n",
    "    select \n",
    "        tianqi, \n",
    "        max(high), \n",
    "        min(low)\n",
    "    from beijing_tianqi\n",
    "    group by tianqi\n",
    "    \"\"\",\n",
    "    con=conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "5510d6d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tianqi</th>\n",
       "      <th>max(high)</th>\n",
       "      <th>min(low)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>中雨</td>\n",
       "      <td>19</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中雨~多云</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>中雨~大雨</td>\n",
       "      <td>26</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>中雨~小雨</td>\n",
       "      <td>24</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>中雨~雷阵雨</td>\n",
       "      <td>27</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tianqi  max(high)  min(low)\n",
       "0      中雨         19        16\n",
       "1   中雨~多云         21         6\n",
       "2   中雨~大雨         26        22\n",
       "3   中雨~小雨         24        20\n",
       "4  中雨~雷阵雨         27        23"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9ea934b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new.to_excel(\"按天气-温度统计.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1357dde",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
